﻿using System;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Text;

using System.Diagnostics;

namespace BestEasy.Data.Test
{
    public static class DbHelper
    {
        /// <summary>
        /// 加载Sql语句
        /// </summary>
        /// <param name="filePath">Sql文件物理路径</param>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        public static ArrayList SqlFileToArrayList(string filePath, string dbName)
        {
            ArrayList alSql = new ArrayList();
            if (!File.Exists(filePath))
            {
                return alSql;
            }
            StreamReader rs = new StreamReader(filePath, Encoding.Default);//注意编码
            string commandText = "";
            string varLine = "";
            while (rs.Peek() > -1)
            {
                varLine = rs.ReadLine();
                if (varLine == "")
                {
                    continue;
                }
                if (varLine != "GO" && varLine != "go")
                {
                    commandText += varLine;
                    commandText = commandText.Replace("@database_name=N'dbhr'", string.Format("@database_name=N'{0}'", dbName));
                    commandText += "\r\n";
                }
                else
                {
                    alSql.Add(commandText);
                    commandText = "";
                }
            }
            rs.Close();
            return alSql;
        }
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="sqlArr">Sql语句数组</param>
        /// <param name="connString">数据库连接字符串</param>
        /// <returns></returns>
        public static bool ExecuteCommand(ArrayList sqlArr, string connString)
        {
            bool flag;
            SqlConnection sqlConn = new SqlConnection(connString);
            Debug.Write("正在打开数据库连接...\n");
            sqlConn.Open();
            SqlTransaction tran = sqlConn.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.Connection = sqlConn;
            command.Transaction = tran;
            try
            {
                foreach (string cmdText in sqlArr)
                {
                    Debug.Write("开始执行Sql语句...\n");
                    command.CommandText = cmdText;
                    var result = command.ExecuteNonQuery();
                    Debug.Write("Sql语句为:\n" + cmdText + "\n");
                    Debug.Write("执行完毕,返回结果：【 "+Convert.ToString(result) + " 】\n");
                }
                tran.Commit();
                flag = true;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                Debug.Write("发生错误啦，错误信息：" + ex.Message+"\n");
                flag = false;
                //throw ex;
            }
            finally
            {
                Debug.Write("正在关闭连接...\n");
                sqlConn.Close();
            }
            Debug.Write("操作完成！\n");
            return flag;
        }
    }
}
